This article is reprinted from the October 1996 issue
of Exploring Oracle Developer/2000 and
Designer/2000, a monthly publication of The Cobb
Group.
Server-side enforcement of complex, interrelated rules, part 1By David Wendelken David Wendelken is a senior consultant for Tactics, Inc. and uses examples like this for creating training materials. He is the co-author of The Oracle Designer/2000 Handbook. You can reach David by phone at (404) 248-1226 or via E-mail at 73523.2344@compuserve.com. Imagine that you want to partition the integrated system you’re building into a set of applications that can be installed in a modular, mix-and-match manner. For example, suppose you’re designing a multi-company accounting system for resale, in which some customers use all of the separate applications, while others want just Payroll or just Job Cost. Let’s explore how we might do that! The Payroll application obviously requires an employee table. So does the Job Cost application. However, the business rules we must enforce for a Job Cost setup (without Payroll) are less stringent than the rules when Payroll is installed. This causes an awkward problem: Do we subject our Job Cost (only) customers to the inconveniences of the far stricter Payroll rules? If not, how do we design our system while satisfying the eight goals in Table A? Table A: These are the goals for our
integrated system.
In this first article in our series on using server-side enforcement, we’ll show how to accomplish the first four goals. Next month, we’ll take care of the next two goals. We’ll take care of the remaining goals in future issues. First, let’s look at the relevant tables. Since our task here is to show how to accomplish these goals, not to build a full-fledged accounting system, our data structures will contain the minimum necessary to demonstrate the concept. Listing A shows the employee table our sample application will be based upon. Listing A: Note that the module would not use this table directly. CREATE TABLE employee (company_id NUMBER(3,0) NOT NULL ,employee_id VARCHAR2(9) NOT NULL ,name VARCHAR2(30) NOT NULL ,annual_salary NUMBER(9,2) ,citizenship VARCHAR2(2)); ALTER TABLE employee ADD (CONSTRAINT emp_pk PRIMARY KEY (company_id,employee_id)); Table B contains the rules for the columns in the employee table, where the rules differ based upon whether Job Cost or Payroll is installed. Table B: These column-validation rules for the EMPLOYEE table differ based on whether Job Cost or Payroll is installed.
We won’t pretend that there’s any valid business rule behind treating annual_salary and citizenship differently when updating an employee record if Job Cost is installed but Payroll is not. However, the difference does illustrate what you can accomplish. Now that we know the rules, let’s start attaining our eight goals. Goal 1: Have the database hide the Payroll-specific columns from Job Cost programs, and vice versa. This is an easy one. As shown in Listing B , we’ll create two views, one for Job Cost programs to use and one for Payroll programs. Why not just create a Job Cost view and let the Payroll programs use the underlying table directly? Because with the next release of our Job Cost system, we’re bound to discover a column that Job Cost users would want, but Payroll (only) users wouldn’t! Listing B: These views hide unwanted columns. /* A view used by Job Cost modules.*/ CREATE VIEW jc_employee AS SELECT company_id ,employee_id ,name FROM employee WITH CHECK OPTION; /* A view used by Payroll modules. */ CREATE VIEW pr_employee AS SELECT company_id ,employee_id ,name ,annual_salary ,citizenship FROM employee WITH CHECK OPTION; Goal 2: Require the database to enforce different rules on new records inserts. To implement this rule, our code will have to know whether Payroll and/or Job Cost are installed. Therefore, a table holding that information would be appropriate. Now that we think about it a bit more, our users might want Payroll installed for one company in their database but not for another company in the same database. Listing C describes a sample table to hold the installation status. Listing C: This is the master control table with one record per company. */ CREATE TABLE company_control (company_id NUMBER(3,0) NOT NULL ,name VARCHAR2(30) NOT NULL ,Payroll_installed_ind CHAR(1) NOT NULL CONSTRAINT comc_cc_pii CHECK (Payroll_installed_ind IN ('Y','N')) ,jobcost_installed_ind CHAR(1) NOT NULL CONSTRAINT comc_cc_jii CHECK (jobcost_installed_ind IN ('Y','N')) ,invalid_employees_ind CHAR(1) NOT NULL CONSTRAINT comc_cc_iei CHECK (invalid_employees_ind IN ('Y','N'))); ALTER TABLE company_control ADD (CONSTRAINT comc_pk PRIMARY KEY (company_id)); /* Let's be tidy and set up foreign keys. */ ALTER TABLE employee ADD (CONSTRAINT emp_fk_comc FOREIGN KEY (company_id) REFERENCES company_control (company_id)); Payroll_installed_ind (indicator) and jobcost_installed_ind record whether, for a given company, Payroll or Job Cost is installed. The application installation programs set these two indicators correctly when their respective subsystems are installed or de-installed. The invalid_employees_ind is set when we install Payroll and find out that pre-existing employee records (created when only Job Cost was installed) lack essential Payroll information. Now that we have a place to indicate whether Payroll or Job Cost is installed, we need to use that information. Listing D contains a brace of database functions that enable us to find out whether Payroll or Job Cost is installed. They are in a package called system_control. This package will grow as we implement additional goals with our server-side code. Listing D: These database functions check the installation status. CREATE OR REPLACE PACKAGE system_control IS - This is a package to demonstrate a - number of techniques. It should - not be used in production without - more robust error tracking. /* Returns (Y)es, (N)o, or (E)rror */ FUNCTION is_Payroll_installed (p_company_id IN NUMBER) RETURN CHAR; /* Returns (Y)es, (N)o, or (E)rror */ FUNCTION is_jobcost_installed (p_company_id IN NUMBER) RETURN CHAR; END; / CREATE OR REPLACE PACKAGE BODY system_control IS FUNCTION is_payroll_installed (p_company_id IN NUMBER) RETURN CHAR IS ws_pr_installed_ind company_control.payroll_installed_ind%TYPE; BEGIN SELECT payroll_installed_ind INTO ws_pr_installed_ind FROM company_control WHERE company_id = p_company_id; RETURN ws_pr_installed_ind; EXCEPTION WHEN OTHERS THEN RETURN 'E'; END; FUNCTION is_jobcost_installed (p_company_id IN NUMBER) RETURN CHAR IS - same idea as is_payroll_installed .. .. END; We now need a database trigger on the employee table to require the database to enforce different rules on new record inserts. Listing E contains a database trigger to implement this goal. We must add that the code is optimized for clarity, not efficiency, and that error-handling has been minimized (for space reasons) below the bounds of user acceptability. Listing E: This trigger enforces goals on inserts of new employee records. CREATE OR REPLACE TRIGGER emp_bir BEFORE INSERT ON employee FOR EACH ROW DECLARE install_status_unknown EXCEPTION; need_data EXCEPTION; unwanted_data EXCEPTION; ws_is_pr_installed company_control.payroll_installed_ind%TYPE := system_control.is_payroll_installed(:new.company_id); ws_is_jc_installed company_control.jobcost_installed_ind%TYPE := system_control.is_jobcost_installed(:new.company_id); BEGIN IF ws_is_pr_installed = 'E' OR ws_is_jc_installed = 'E' THEN RAISE install_status_unknown; END IF; - There are four remaining combinations. IF ws_is_pr_installed = 'Y' AND ws_is_jc_installed = 'Y' THEN - Citizenship is mandatory. IF :new.citizenship IS NULL THEN RAISE need_data; END IF; END IF; IF ws_is_pr_installed = 'Y' AND ws_is_jc_installed = 'N' THEN - Citizenship is mandatory. IF :new.citizenship IS NULL THEN RAISE need_data; END IF; END IF; IF ws_is_pr_installed = 'N' AND ws_is_jc_installed = 'Y' THEN - We don't want citizenship or annual_salary data. IF :new.annual_salary IS NOT NULL OR :new.citizenship IS NOT NULL THEN RAISE unwanted_data; END IF; END IF; IF ws_is_pr_installed = 'N' AND ws_is_jc_installed = 'N' THEN - We don't record employees at all. RAISE unwanted_data; END IF; EXCEPTION WHEN install_status_unknown THEN RAISE_APPLICATION_ERROR(-20001,'Install status unknown.'); WHEN unwanted_data THEN RAISE_APPLICATION_ERROR(-20001,'Unwanted data.'); WHEN need_data THEN RAISE_APPLICATION_ERROR(-20001,'Need more data.'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,SQLCODE ││ ':' ││ SQLERRM); END; / Goal 3: Enforce different rules on updates of existing records, based upon whether Payroll and/or Job Cost are installed. Goal 4: Allow incremental updates to improve employee data for existing records. Goal 3 is similar to the problem we faced when
inserting new records. Goal 4 complicates this
programming task, but doesn’t conceptually alter
what needs to be done. Let’s recap the logic of the
situation we could be placed in when the installation
status of Payroll or Job Cost changes:
Listing F shows the implementation of Goals 3 and 4. At this point, we’ve achieved our first four goals. Not bad for a couple of triggers, two views, and a simple package! Next month, we’ll travel further down our eight-fold path to server-side enlightenment. Listing F: This trigger enforces goals on updates of existing employee records. CREATE OR REPLACE TRIGGER emp_bur BEFORE UPDATE ON employee FOR EACH ROW DECLARE install_status_unknown EXCEPTION; need_data EXCEPTION; unwanted_data EXCEPTION; no_change_allowed EXCEPTION; ws_is_pr_installed company_control.Payroll_installed_ind%TYPE := system_control.is_Payroll_installed(:new.company_id); ws_is_jc_installed company_control.jobcost_installed_ind%TYPE := system_control.is_jobcost_installed(:new.company_id); BEGIN IF ws_is_pr_installed = 'E' OR ws_is_jc_installed = 'E' THEN RAISE install_status_unknown; END IF; - There are four more - combinations. IF ws_is_pr_installed = 'Y' AND ws_is_jc_installed = 'Y' THEN - Citizen is mandatory, unless - already blank. - (Incremental update.) IF :new.citizenship IS NULL AND :old.citizenship IS NOT NULL THEN RAISE need_data; END IF; END IF; IF ws_is_pr_installed = 'Y' AND ws_is_jc_installed = 'N' THEN - Citizen is mandatory, unless - already blank. - (Incremental update.) IF :new.citizenship IS NULL AND :old.citizenship IS NOT NULL THEN RAISE need_data; END IF; END IF; IF ws_is_pr_installed = 'N' AND ws_is_jc_installed = 'Y' THEN - Don't want annual_salary - recorded, but don't change - it if already there. IF ( :new.annual_salary IS NOT NULL AND :old.annual_salary IS NOT NULL AND :old.annual_salary != :new.annual_salary ) OR ( :new.annual_salary IS NULL AND :old.annual_salary IS NOT NULL ) OR ( :new.annual_salary IS NOT NULL AND :old.annual_salary IS NULL ) THEN RAISE no_change_allowed; END IF; - Make citizenship blank, regardless of old value. :new.citizenship := NULL; END IF; IF ws_is_pr_installed = 'N' AND ws_is_jc_installed = 'N' THEN - We don't record employees at all. RAISE unwanted_data; END IF; EXCEPTION WHEN install_status_unknown THEN RAISE_APPLICATION_ERROR(-20001,'Install status unknown.'); WHEN unwanted_data THEN RAISE_APPLICATION_ERROR(-20001,'Unwanted data.'); WHEN need_data THEN RAISE_APPLICATION_ERROR(-20001,'Need more data.'); WHEN no_change_allowed THEN RAISE_APPLICATION_ERROR(-20001,'No change allowed.'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM); END; / |
||||||||||||||||||||||||||||||||||||||||||||||
Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company. Questions? Comments? |